<?php

 function delRow($table, $IDName, $ID){
    global $dbSet ;
    $dbSet->execute("DELETE FROM ".$table." WHERE ".$IDName."='".$ID."'");
  }


  function delRows($table, $columnID, $ids){
    global $dbSet ;

    if (empty($ids)) return 0;
    $inIDs = implode("','",$ids);

    $dbSet->execute("DELETE FROM ".$table." WHERE $columnID IN ('".$inIDs."')");
    return 1;
  }

  function makeInsertList(&$strColumns,&$strValues, $result, $MySQLFuncColumns=array(),$breakColumns=array())
  {
    $strColumns=""; $strValues="";
    foreach($result as $key => $val)
    {
      if (in_array($key,$breakColumns)) continue;
      $v = addslashes($val);
      if (in_array($key,$MySQLFuncColumns))
      {      	$strValues .=  " $v ,";
      }
      else
      {
        $strValues .=  " '$v' ,";
      }
      $strColumns .= " `$key` ,";
    }
    $strValues = substr($strValues,0,strlen($strValues)-1);
    $strColumns = substr($strColumns,0,strlen($strColumns)-1);
  }

  function makeUpdateList(&$strSet, $result, $MySQLFuncColumns=array(), $breakColumns=array())
  {
	$strSet="";
	foreach($result as $key => $val)
	{
	  if (in_array($key,$breakColumns)) continue;
	  $v = addslashes($val);
	  if (in_array($key,$MySQLFuncColumns))
 	  {
      	$strSet .= " `$key` = $v ,";
      }
      else
      {
        $strSet .= " `$key` = '$v' ,";
      }
    }
	$strSet = substr($strSet,0,strlen($strSet)-1);
  }

  function makeSearchList(&$strSet, $result, $MySQLFuncColumns=array(), $breakColumns=array())
  {
	$strSet="";
	foreach($result as $key => $val)
	{
	  if($flag == 1)
	  {	    $strSet .= "AND";
	  }
	  if (in_array($key,$breakColumns)) continue;
	  $v = addslashes($val);
	  if (in_array($key,$MySQLFuncColumns))
 	  {
      	$strSet .= " `$key` = $v ";
      }
      else
      {
        $strSet .= " `$key` = '$v' ";
      }
	  $flag = 1;
	}
  }

  function getListFull($from, &$pageArr, $fields='', $taborder='', $where1='', $groupby='')
  {
  	global $dbSet, $CONFIG, $WorkTable, $WorkTableKeyFieldName;

    if(isBlank($fields))
    {
      $fields ='*';
    }
    if(isBlank($from))
    {
      $from = $WorkTable;
    }
    if(!isBlank($where1))
    {
      $Where = " WHERE ".$where1;
    }
    if(!isBlank($groupby))
    {
      $groupby = " GROUP BY ".$groupby;
    }
    foreach($pageArr as $name => $value)
  	{
  	  $$name = $value;
  	}
  	if(!isEmptyArr($where))
  	{
  		$whereArr = array();
  		foreach($where as $name => $arr)
  	  {
  	  	if(isset($arr['forSQL']) && $arr['forSQL'])
  	  	{
  	  	  array_push($whereArr, $arr['forSQL']);
  	  	}
  	  }
  	  $Where = (($Where) ? $Where : '').(sizeof($whereArr) ? (($Where) ? ' AND ' : ' WHERE ').join(" AND ", $whereArr) : '');
  	}
  	if(!isBlank($tabord))
  	{
  	  if($tabord == '_')
  	  {
  	  	$tabord = "";
  	  }
  	  $taborder = $tabord;
  	}
  	if(isBlank($order_type))
    {
      $order_type = "ASC";
    }
    $order_type = " ".strToUpperCase($order_type);
    if(isBlank($order))
    {
      $order_for_page = $order = ((isBlank($WorkTableKeyFieldName)) ? $CONFIG['sortByDefault'] : $WorkTableKeyFieldName);
      $order_parts = explode(',', $order);
      foreach($order_parts as $n => $str)
      {
        if(!preg_match("/\sasc\s*$|\sdesc\s*$/is", trim($str)))
        {
          $order_parts[$n] .= $order_type;
        }
      }
      $order = join(',', $order_parts);
    }
    else
    {
      $order = str_replace($CONFIG['AdminLinkNameDelim'], ' ', $order);
      $order_for_page = $order;
      $order_parts = explode(',', $order);
      foreach($order_parts as $n => $str)
      {
        if(!preg_match("/\sasc\s*$|\sdesc\s*$/is", trim($str)))
        {
          $order_parts[$n] .= $order_type;
        }
      }
      $order = join(',', $order_parts);
    }
    if(isBlank($onpage))
    {
      $onpage = $CONFIG['onPage'];
    }
    if(intval($page) < 1) $page = 1;

    $Query = "SELECT ".$fields." FROM ".$from.$Where." ".$groupby." ORDER BY ".$taborder.$order." LIMIT ".($onpage*($page - 1)).", ".$onpage;
  	$query = str_replace("SELECT", "SELECT SQL_CALC_FOUND_ROWS", $Query);
  	$dbSet->execute($query);
  	$query = "SELECT FOUND_ROWS()";
  	$dbSet->open($query);
  	$count = $dbSet->loadResult();

  	$totalPages = ceil($count / $onpage);
  	if ($page > $totalPages && $page > 1) $page = $totalPages;

  	$pages=array();
	$page1 = $page - $CONFIG['pageRange'];

	if($page > ($totalPages - $CONFIG['pageRange']))
	{
	  $page1 = $totalPages - $CONFIG['pageRange']*2 + 1;
	}
	$prev = ($page > 1 && $page <= $totalPages) ? ($page - 1) : 0;
	if($page1 < 1) $page1 = 1;
    for($i = $page1; $i < ($page1 + $CONFIG['pageRange'] * 2) && $i <= $totalPages; $i++)
	{
       $pages[]=$i;
	}
    $next = (($page + 1)<= $totalPages && $page > 0) ? ($page + 1) : 0;
    $counterStart = ($page - 1) * $onpage;

    $pageArr['order'] = $order_for_page;
    $pageArr['page'] = $page;
    $pageArr['onpage'] = $onpage;
    $pageArr['pages'] = $pages;
    $pageArr['prev'] = $prev;
    $pageArr['next'] = $next;
    $pageArr['lastpage'] = $totalPages;
    $pageArr['firstpage'] = 1;
    $pageArr['count'] = $count;
    $pageArr['countSt'] = $counterStart;

    $dbSet->open($Query);
  	return ($count > 0) ? $dbSet->fetchRowsAll() : array();
  }


  function getRowByField($table, $fieldname, $fieldvalue)
  {
    global $dbSet;
    $dbSet->open("SELECT * FROM ".$table." WHERE ".$fieldname." = '".$fieldvalue."'");
    return $dbSet->fetchArray();
  }

  function getRowByStrSet($table, $strSet)
  {
    global $dbSet;
    $dbSet->open("SELECT * FROM ".$table." WHERE ".$strSet);
    return $dbSet->fetchArray();
  }

  function getRowByFields($table, $fields = array(), $orderby='')
  {
    global $dbSet;
    $dbSet->open("SELECT * FROM ".$table." WHERE ".join(" AND ", $fields)." ".$orderby);
    return $dbSet->fetchArray();
  }

  function getRowsByField($table, $fieldname, $fieldvalue, $orderby='')
  {
    global $dbSet;
    $dbSet->open("SELECT * FROM ".$table." WHERE ".$fieldname." = '".$fieldvalue."' ".$orderby);
    return $dbSet->fetchRowsAll();
  }

  function getFieldByEnother($fieldname1, $table, $fieldname, $fieldvalue)
  {  	$arr = getRowByField($table, $fieldname, $fieldvalue);
  	return $arr[$fieldname1];
  }

  function getTableAsFolders($FolderTbl, $FolderTblKeyFld='id', $FolderTblRootFld='root', $FolderTblOrder='', $FolderTblParams=array(), $FolderTblRoot=0, $FileFlag=__FALSE, $FileTbl='', $FileTblRootFld='folder')
  {
    global $CONFIG, $ERRORS;

    $Where = array_merge($FolderTblParams, array($FolderTblRootFld." = '".$FolderTblRoot."'"));
    $Folders = getTableAsArray($FolderTbl, $FolderTblOrder, $Where);
    foreach($Folders as $n => $Folder)
    {
      $Folders[$n]['folders'] = getTableAsFolders($FolderTbl, $FolderTblKeyFld, $FolderTblRootFld, $FolderTblOrder, $FolderTblParams, $Folder[$FolderTblKeyFld], $FileFlag, $FileTbl);
      if($FileFlag == __TRUE)
      {
        $Folders[$n]['rows'] = getRowsByField($FileTbl, $FileTblRootFld, $Folder[$FolderTblKeyFld]);
      }
    }
    return $Folders;
  }

  function getTableAsArray($table, $order='', $where = array(), $limit='', $fields='*', $distinct='', $group='')
  {
    global $dbSet;
    if(!isBlank($order))
    {
      $query_order = " ORDER BY ".$order;
    }
    if(!isBlank($group))
    {
      $query_group = " ".$group;
    }
    if(!isBlank($limit))
    {
      $query_limit = " ".$limit;
    }
    if(!isBlank($distinct))
    {
      $distinct = "DISTINCT ";
    }
    if(!isEmptyArr($where))
    {
      $query_where = " WHERE ".join(' AND ', $where);
    }
    $dbSet->open("SELECT ".$distinct.$fields." FROM ".$table.$query_where.$query_group.$query_order.$query_limit);
    return $dbSet->fetchRowsAll();
  }
  function getTableAsArrayByKeyField($table, $keyfield, $order='', $where = array(), $limit='', $fields='*', $distinct='', $group='')
  {  	$result = getTableAsArray($table, $order, $where, $limit, $fields, $distinct, $group);
  	foreach($result as $n => $arr)
    {
  	  $Items[$arr[$keyfield]] = $arr;
    }
    return $Items;
  }

  function updateItem($table, &$strSet, $fieldname, $fieldvalue)
  {
    global $dbSet;
    $dbSet->execute("UPDATE ".$table." SET ".$strSet." WHERE ".$fieldname."='".$fieldvalue."'");
  }
  function updateItemByFields($table, &$strSet, $where=array())
  {
    global $dbSet;
    $dbSet->execute("UPDATE ".$table." SET ".$strSet." WHERE ".join(' AND ', $where));
  }

  function insertItem($table, &$strColumns,&$strValues)
  {
    global $dbSet;
    return $dbSet->execute("INSERT INTO ".$table." (".$strColumns.") VALUES (".$strValues.")");
  }

  function makeListOfRelations($tableInfo=array(), $resultValues=array())
  {
    /* Example of $tableInfo */
    /*
      $tableInfo = array(
  						'name' => $_SQL_TABLE['subdomain_brand'],
  						'keyField' => 'subdomain',
  						'keyFieldCol' => 'code',
  						'relatedField' => 'brand',
  						'keyFieldValue' => $_REQUEST[id],
  						'dateField' => 'y/n', // is date field is presented
  					);
  	  $resultValues = array(1,2,3,45);
    */

    global $dbSet, $CONFIG;
    $dbSet->open("SELECT ".$tableInfo['relatedField'].", active FROM ".$tableInfo['name']." WHERE ".$tableInfo['keyField']." = '".$tableInfo['keyFieldValue']."'");
    $result = $dbSet->fetchRowsAll();
    $existVals = array();
    $existValsStatus = array();
    foreach($result as $n => $arr)
    {
      $existVals[] = $arr[$tableInfo['relatedField']];
      $existValsStatus[$arr[$tableInfo['relatedField']]] = $arr['active'];
    }
    $newVals = array_diff($resultValues, $existVals);
    foreach($newVals as $n => $val)
    {
      if(!array_key_exists($val, $existValsStatus))
      {
        $vals = array(
      					$tableInfo['relatedField'] => $val,
      					$tableInfo['keyField'] => $tableInfo['keyFieldValue'],
      					'active' => 'y',
      				);
      	if($tableInfo['dateField'] == 'y')
      	{      	  $vals['date'] = 'NOW()';
      	}
        makeInsertList($strColumns,$strValues, $vals, array('date'));
        insertItem($tableInfo['name'], $strColumns, $strValues);
      }
    }
    foreach($existValsStatus as $val => $status)
    {
      if(!in_array($val, $resultValues))
      {
        $vals = array(
      					'active' => 'n',
  		  		  );
  	  }
  	  elseif(in_array($val, $resultValues))
  	  {
  	    $vals = array(
      					'active' => 'y',
  		  		  );
  	  }
  	  if($vals['active'] != $existValsStatus[$val])
  	  {
        makeUpdateList($strSet, $vals);
        updateItemByFields($tableInfo['name'], $strSet, array($tableInfo['relatedField']." = '".$val."'", $tableInfo['keyField']." = '".$tableInfo['keyFieldValue']."'"));
      }
    }
  }

  function makeListOfRelationsIdsOnly($tableInfo=array(), $resultValues=array())
  {

  	print_r($tableInfo);
    print_r($resultValues);
    exit();

    /* Example of $tableInfo */
    /*
      $tableInfo = array(
  						'name' => $_SQL_TABLE['subdomain_brand'],
  						'keyField' => 'subdomain',
  						'keyFieldCol' => 'code',
  						'relatedField' => 'brand',
  						'keyFieldValue' => $_REQUEST[id],
  						'dateField' => 'y/n', // is date field is presented
  					);
  	  $resultValues = array(1,2,3,45);
    */

    global $dbSet, $CONFIG;
    $dbSet->open("SELECT ".$tableInfo['relatedField'].", active FROM ".$tableInfo['name']." WHERE ".$tableInfo['keyField']." = '".$tableInfo['keyFieldValue']."'");
    $result = $dbSet->fetchRowsAll();
    $existVals = array();
    $existValsStatus = array();
    foreach($result as $n => $arr)
    {
      $existVals[] = $arr[$tableInfo['relatedField']];
      $existValsStatus[$arr[$tableInfo['relatedField']]] = $arr['active'];
    }
    $newVals = array_diff($resultValues, $existVals);
    foreach($newVals as $n => $val)
    {
      if(!array_key_exists($val, $existValsStatus))
      {
        $vals = array(
      					$tableInfo['relatedField'] => $val,
      					$tableInfo['keyField'] => $tableInfo['keyFieldValue'],
      					'active' => 'y',
      				);
      	if($tableInfo['dateField'] == 'y')
      	{
      	  $vals['date'] = 'NOW()';
      	}
        makeInsertList($strColumns,$strValues, $vals, array('date'));
        insertItem($tableInfo['name'], $strColumns, $strValues);
      }
    }
    foreach($existValsStatus as $val => $status)
    {
      if(!in_array($val, $resultValues))
      {
        $vals = array(
      					'active' => 'n',
  		  		  );
  	  }
  	  elseif(in_array($val, $resultValues))
  	  {
  	    $vals = array(
      					'active' => 'y',
  		  		  );
  	  }
  	  if($vals['active'] != $existValsStatus[$val])
  	  {
        makeUpdateList($strSet, $vals);
        updateItemByFields($tableInfo['name'], $strSet, array($tableInfo['relatedField']." = '".$val."'", $tableInfo['keyField']." = '".$tableInfo['keyFieldValue']."'"));
      }
    }
  }

  function makeRelation($tableInfo=array(), $resultValue)
  {
    /* Example of $tableInfo */
    /*
      $tableInfo = array(
  						'name' => $_SQL_TABLE['subdomain_brand'],
  						'keyField' => 'subdomain',
  						'keyFieldCol' => 'code',
  						'relatedField' => 'brand', // not required
  						'keyFieldValue' => $_REQUEST[id],
  						'dateField' => 'y/n', // is date field is presented
  					);
  	  $resultValue = 'y';
    */

    global $dbSet, $CONFIG;
    $dbSet->open("SELECT id, active FROM ".$tableInfo['name']." WHERE ".$tableInfo['keyField']." = '".$tableInfo['keyFieldValue']."'");
    $result = $dbSet->fetchArray();
    if(isBlank($result['id']))
    {
      if(!isBlank($resultValue))
      {
        $vals = array(
      					$tableInfo['keyField'] => $tableInfo['keyFieldValue'],
      					'active' => 'y',
      				);
        if($tableInfo['dateField'] == 'y')
        {
          $vals['date'] = 'NOW()';
        }
        if(!isBlank($tableInfo['relatedField']))
  	    {          $vals[$tableInfo['relatedField']] = $resultValue;
          if(isBlank($resultValue))
          {            $vals['active'] = 'n';
          }
        }
        makeInsertList($strColumns,$strValues, $vals, array('date'));
        insertItem($tableInfo['name'], $strColumns, $strValues);
      }
    }
    else
    {
      if(isBlank($resultValue))
      {
        $vals = array(
      					'active' => 'n',
  		  		  );
  	  }
  	  else
  	  {
  	    $vals = array(
      					'active' => 'y',
  		  		  );
  	    if(!isBlank($tableInfo['relatedField']))
  	    {
  	      $vals[$tableInfo['relatedField']] = $resultValue;
  	    }
  	  }
  	  makeUpdateList($strSet, $vals);
      $Conditions = array($tableInfo['keyField']." = '".$tableInfo['keyFieldValue']."'");
      updateItemByFields($tableInfo['name'], $strSet, $Conditions);
    }
  }

  function createTable($tblName, $fields = array(), $indexes = array(), $params = array())
  {    global $CONFIG, $dbSet;

    $i=0;
    foreach($fields as $name => $value)
    {      $fields_str .= (($i > 0) ? ',' : '')."`".$name."` ".$value;
      $i++;
    }
    if(count($indexes) > 0)
    {
      $fields_str .= ','.join(',',$indexes);
    }
    $i=0;
    foreach($params as $name => $value)
    {
      $params_str .= (($i > 0) ? ' ' : '').$name.'='.$value;
      $i++;
    }
    $query = "
        		CREATE TABLE IF NOT EXISTS ".$tblName."
        		".((count($fields) > 0) ? "(".$fields_str.")" : "")."
        		".((count($params) > 0) ? $params_str : "" )."
        		;";
    $dbSet->execute($query);
  }

  function tableExists($tblName, $dbName='')
  {  	global $dbSet;

  	return $dbSet->tableExist($tblName, $dbName);
  }

  function DBExists($dbName='')
  {
  	global $dbSet;

    return $dbSet->DBExist($dbName);
  }

  function createDB($dbName)
  {    global $dbSet;

    $Error_reporting = $dbSet->get_error_reporting();
    $dbSet->set_error_reporting('');
    $query = "CREATE DATABASE IF NOT EXISTS ".$dbName;
    $result = $dbSet->execute($query);
    $dbSet->set_error_reporting($Error_reporting);
    return $result;
  }

  function deleteTable($tblName)
  {    global $dbSet;

    if(is_array($tblName) && count($tblName) > 0)
    {      $table = join(',', $tblName);
    }
    elseif(is_string($tblName) && strlen($tblName) > 0)
    {      $table = $tblName;
    }
    if(strlen($table) > 0)
    {
      $query = "DROP TABLE IF EXISTS ".$table;
      $dbSet->execute($query);
    }
  }

  function renameTable($TableNameOld, $TableNameNew, $DBName='')
  {    global $CONFIG, $dbSet;

    if(!isBlank($DBName))
    {      $DBName = "`".$DBName."`.";
    }
  	$query = "RENAME TABLE ".$DBName."`".$TableNameOld."`  TO ".$DBName."`".$TableNameNew."`";
  	$dbSet->execute($query);
  }

  function makeTableNameFromParts($tabNameParts)
  {
    global $CONFIG, $dbSet;

    return make_tablename(join($CONFIG['AdminDBTableNameDelim'], $tabNameParts));
  }

?>